package com.etooling.framework.utils;

import com.alibaba.fastjson.JSONArray;
import com.alibaba.fastjson.JSONObject;
import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;
import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.web.multipart.MultipartFile;
import org.springframework.web.multipart.MultipartHttpServletRequest;

import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.*;
import java.math.BigDecimal;
import java.nio.channels.FileChannel;
import java.sql.*;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.Map;
import java.util.regex.Pattern;

/**
 * @author wuhaixin 需要jar包 poi-3.8-20120326.jar poi-ooxml-3.8-20120326.jar
 * poi-ooxml-schemas-3.8-20120326.jar xbean.jar xmlbeans-2.3.0.jar
 */
public class ExcelReportUtils {

    private static Log log = LogFactory.getLog(ExcelReportUtils.class);

    @Autowired
    private JdbcTemplate jdbcTemplate;

    /**
     * 使用NIO中的管道到管道传输 文件复制
     *
     * @param f1
     * @param f2
     * @return
     * @throws Exception
     */
    public boolean copyFile(String f1, String f2) throws Exception {

        File f1_ = new File(f1);
        // 源文件不存在则返回
        if (!f1_.exists())
            return false;
        File f2_ = new File(f2);
        int length = 2097152;
        FileInputStream in = new FileInputStream(f1_);
        FileOutputStream out = new FileOutputStream(f2_);
        FileChannel inC = in.getChannel();
        FileChannel outC = out.getChannel();
        while (true) {
            if (inC.position() == inC.size()) {
                inC.close();
                outC.close();
                in.close();
                out.close();
                return true;
            }
            if ((inC.size() - inC.position()) < 20971520)
                length = (int) (inC.size() - inC.position());
            else
                length = 20971520;
            inC.transferTo(inC.position(), length, outC);
            inC.position(inC.position() + length);
        }

    }

    /**
     * 支持03及07excel 03支持最大65000行,07最大支持1048500行 往Excel添加报表数据
     *
     * @param filePath 要修改的文件路径.
     * @throws IOException
     */
    public void fillDataToExcel(String filePath, Connection conn, String versionCode) throws IOException {

        long time1 = System.currentTimeMillis();
        int index = filePath.lastIndexOf(".");
        String suffix = ""; // 文件后缀
        if (index != -1) {
            suffix = filePath.substring(index, filePath.length());
        }
        FileInputStream fis = new FileInputStream(filePath);

        Workbook wb = null;
        if (".xlsx".equalsIgnoreCase(suffix)) {
            wb = new XSSFWorkbook(fis);
        } else {
            wb = new HSSFWorkbook(fis);
        }
        StrHandle strHandle = new StrHandle();

        // 创建style速度太慢. 循环前创建一个空的备用.
        CellStyle tempStyle = wb.createCellStyle();

        // 循环模板的 sheet数量.
        int sheetNums = wb.getNumberOfSheets();
        for (int i = 0; i < sheetNums; i++) {
            Sheet sheet = wb.getSheetAt(i);
            // 拿出模板
            int firstRow = sheet.getFirstRowNum();
            int lastRow = sheet.getLastRowNum();
            if ((firstRow == -1) || (lastRow == -1) || lastRow < firstRow) {
                return;
            }

            // 合计列的多少(适应动态表头,用来生成合计列的sum公式)
            int rowOfSumsCount = 0;

            // 行是否创建成功
            boolean isSuccess = true;
            for (int r = 0; r <= lastRow; r++) {
                Row row = sheet.getRow(r);
                if (row == null) {
                    continue;
                } else {
                    for (int j = row.getFirstCellNum(); j < row.getLastCellNum(); j++) {
                        Cell cell = row.getCell(j);
                        if (cell == null) {
                            continue;
                        }
                        CellType cType = cell.getCellType();
                        CellStyle cellStyle = null;
                        try {
                            cellStyle = cell.getCellStyle();
                        } catch (Exception e) {
                            log.info(e.getMessage());
                            // e.printStackTrace();
                        }
                        switch (cType) {
                            case STRING:
                                String cellValue = cell.getRichStringCellValue().getString();
                                // 先替换versionCode
                                if (cellValue.indexOf("$_") != -1) {// 处理模sr
                                    cellValue = strHandle.handleDate(cellValue, versionCode, "yyyyMM");
                                    cell.setCellValue(cellValue);
                                }
                                // data 数据sql, statistic 统计sql
                                if (cellValue.startsWith("$_sql")) {// 处理模sr
                                    // 填充数据sql.
                                    String sql = cellValue.replace("$_sql:", "");
                                    if (sql.lastIndexOf(";") > 0) {
                                        sql = sql.substring(0, sql.length() - 1);
                                    }
                                    tempStyle = cellStyle;
                                    // sql = sql.toUpperCase();
                                    sql = sql.replaceAll("\n", " ");
                                    String countSql = "SELECT COUNT(*) FROM ( " + sql + ")";

                                    // log.info(("报表填充sql为:"+sql));
                                    // 计算结果行数
                                    PreparedStatement presmt = null;
                                    ResultSet rs = null;
                                    ResultSet rs2 = null;
                                    Statement stmt = null;
                                    try {
                                        // 获取总记录数
                                        stmt = conn.createStatement();
                                        rs2 = stmt.executeQuery(countSql);
                                        int total = 0;
                                        if (rs2.next()) {
                                            total = rs2.getInt(1);
                                        }
                                        rs2.close();
                                        stmt.close();

                                        if (total == 0) {
                                            cell.setCellValue("");
                                        }

                                        presmt = conn.prepareStatement(sql);
                                        rs = presmt.executeQuery();

                                        long rows = total;
                                        ResultSetMetaData metadata = rs.getMetaData();
                                        int count = metadata.getColumnCount();
                                        //
                                        rowOfSumsCount = count;
                                        boolean flag = false;
                                        if (".xlsx".equalsIgnoreCase(suffix)) {
                                            flag = rows < 65500;
                                        } else {
                                            flag = rows < 1048500;
                                        }
                                        if (flag) {
                                            int srow = 0;
                                            // 当前单元格所在列数.
                                            int curCell = cell.getColumnIndex();
                                            // Excel生成时异常处理, 如果超过Excel限制行数 则停止填充类容.
                                            try {
                                                boolean hasShift = false;
                                                while (rs.next()) {
                                                    Row newRow;
                                                    if (srow == 0) {
                                                        newRow = row;
                                                    } else {
                                                        if (!hasShift) {
                                                            try {
                                                                sheet.shiftRows(r + 1, lastRow + 1, ((int) rows) - 1);
                                                            } catch (Exception e) {
                                                                e.printStackTrace();
                                                                break;
                                                            }
                                                            hasShift = true;
                                                        }
                                                        newRow = sheet.createRow(r + (srow));
                                                    }
                                                    srow++;
                                                    for (int scell = 0; scell < count; scell++) {
                                                        // 获取模板文件原单元格报表格式.
                                                        Cell oldCell = row.getCell((scell + curCell));
                                                        CellStyle oldStyle = null;
                                                        if (oldCell != null) {
                                                            oldStyle = oldCell.getCellStyle();
                                                        }
                                                        if (oldStyle == null) {
                                                            if (cellStyle == null) {
                                                                oldStyle = cellStyle;
                                                            } else {
                                                                oldStyle = tempStyle;
                                                            }
                                                        }

                                                        Cell addCell = newRow.createCell((scell + curCell));
                                                        addCell.setCellStyle(oldStyle);
                                                        // 数据库获取的值.
                                                        String str = rs.getString(scell + 1);

                                                        // 根据数据库查询结果类型格式
                                                        if (Types.VARBINARY == metadata.getColumnType(scell + 1)) {
                                                            // 默认填写为数字格式.
                                                            if (isNum(str)) {
                                                                addCell.setCellValue(Double.parseDouble(str));
                                                            } else {
                                                                addCell.setCellValue(str);
                                                            }
                                                        } else if (Types.DECIMAL == metadata.getColumnType(scell + 1)
                                                                || Types.DOUBLE == metadata.getColumnType(scell + 1)
                                                                || Types.FLOAT == metadata.getColumnType(scell + 1)
                                                                || Types.INTEGER == metadata.getColumnType(scell + 1)
                                                                || Types.TINYINT == metadata.getColumnType(scell + 1)
                                                                || Types.NUMERIC == metadata.getColumnType(scell + 1)
                                                                || Types.BIGINT == metadata.getColumnType(scell + 1)
                                                                || Types.SMALLINT == metadata.getColumnType(scell + 1)) {
                                                            // 样式与原单元格保持一致
                                                            if (oldStyle == null) {
                                                                cellStyle.setAlignment(HorizontalAlignment.RIGHT);
                                                                addCell.setCellStyle(cellStyle);
                                                            }
                                                            // 根据数据库列类型进行赋值
                                                            if (str == null) {
                                                                addCell.setCellValue(str);
                                                            } else {
                                                                addCell.setCellValue(Double.parseDouble(str));
                                                            }
                                                        } else {
                                                            // 样式与原单元格保持一致
                                                            if (oldStyle == null) {
                                                                cellStyle.setAlignment(HorizontalAlignment.LEFT);
                                                                addCell.setCellStyle(cellStyle);
                                                            }
                                                            // 默认填写为数字格式.
                                                            if (isNum(str)) {
                                                                addCell.setCellValue(Double.parseDouble(str));
                                                            } else {
                                                                addCell.setCellValue(str);
                                                            }
                                                        }
                                                    }
                                                }
                                                rs.close();
                                                presmt.close();
                                            } catch (Exception e) {
                                                e.printStackTrace();
                                                isSuccess = false;
                                            }
                                        }
                                        if (isSuccess)
                                            lastRow = lastRow + (int) rows + 1;// 最后一行向下走count+1
                                    } catch (SQLException e) {
                                        e.printStackTrace();
                                    }
                                }
                                // 向上累加 $_sumup向上累加标记,转换为 =SUM(A1:An)公式
                                if ("$_sumup".equalsIgnoreCase(cellValue)) {
                                    int colIndex = cell.getColumnIndex() + 1;
                                    String colName = convertColIndexNumber2String(colIndex);
                                    String formula = "SUM(" + colName + "1:" + colName + (r) + ")";
                                    cell.setCellFormula(formula);
                                }

                                // 适应动态表头.
                                int curCellIndex = cell.getColumnIndex();
                                if ("$_sumups".equalsIgnoreCase(cellValue) && rowOfSumsCount >= (curCellIndex + 1)) {
                                    CellStyle sumStyle = cell.getCellStyle();
                                    for (int m = 0; m <= (rowOfSumsCount - curCellIndex); m++) {
                                        Cell tempCell = row.getCell(curCellIndex + m);
                                        String colName = convertColIndexNumber2String(curCellIndex + 1 + m);
                                        String formula = "SUM(" + colName + "1:" + colName + (r) + ")";
                                        if (tempCell != null) {
                                            tempCell.setCellFormula(formula);
                                        } else {
                                            Cell addCell = row.createCell(curCellIndex + m);
                                            addCell.setCellStyle(sumStyle);
                                            addCell.setCellFormula(formula);
                                        }
                                    }
                                }
                                break;
                        }
                    }
                }

            }
            // 执行公式
            sheet.setForceFormulaRecalculation(true);
        }
        long time2 = System.currentTimeMillis();
        log.info("已经生成Excel报表文件." + "所耗时间为:" + (time2 - time1));
        BufferedOutputStream buffer = new BufferedOutputStream(new FileOutputStream(filePath));
        wb.write(buffer);
        buffer.flush();
        buffer.close();

        // 开始: 1332808967326 结束:1332808968201 耗时:875
    }


    private static final String XLSX = ".xlsx";
    private static final String XLS = ".xls";

    public static JSONArray readMultipartFile(MultipartFile mFile) throws Exception {
        return readExcel(mFile, null);
    }

    private static JSONArray readExcel(MultipartFile mFile, File file) throws IOException {
        Workbook book = getWorkbook(mFile, file);
        if (book == null) {
            return new JSONArray();
        }
        JSONArray array = readSheet(book.getSheetAt(0));
        book.close();
        return array;
    }

    private static Workbook getWorkbook(MultipartFile mFile, File file) throws IOException {
        boolean fileNotExist = (file == null || !file.exists());
        if (mFile == null && fileNotExist) {
            return null;
        }
        // 解析表格数据
        InputStream in;
        String fileName;
        if (mFile != null) {
            // 上传文件解析
            in = mFile.getInputStream();
            fileName = getString(mFile.getOriginalFilename()).toLowerCase();
        } else {
            // 本地文件解析
            in = new FileInputStream(file);
            fileName = file.getName().toLowerCase();
        }
        Workbook book;
        if (fileName.endsWith(XLSX)) {
            book = new XSSFWorkbook(in);
        } else if (fileName.endsWith(XLS)) {
            POIFSFileSystem poifsFileSystem = new POIFSFileSystem(in);
            book = new HSSFWorkbook(poifsFileSystem);
        } else {
            return null;
        }
        in.close();
        return book;
    }

    private static JSONArray readSheet(Sheet sheet) {
        // 首行下标
        int rowStart = sheet.getFirstRowNum();
        // 获取表头行
        Row headRow = sheet.getRow(rowStart);
        if (headRow == null) {
            return new JSONArray();
        }
        int cellStart = headRow.getFirstCellNum();
        int cellEnd = headRow.getLastCellNum();
        JSONArray array1 = new JSONArray();
        for (int j = cellStart; j < cellEnd; j++) {
            // 获取表头数据
            String val = getCellValue(headRow.getCell(j));
            if (val != null && val.trim().length() != 0) {
                JSONObject obj = new JSONObject();
                obj.put(j + "", val);
                array1.add(obj);
            }
        }
        return array1;
    }

    private static String getCellValue(Cell cell) {
        // 空白或空
        if (cell == null || cell.getCellType() == CellType.BLANK) {
            return "";
        }
        // String类型
        if (cell.getCellType() == CellType.STRING) {
            String val = cell.getStringCellValue();
            if (val == null || val.trim().length() == 0) {
                return "";
            }
            return val.trim();
        }
        // 数字类型
        if (cell.getCellType() == CellType.NUMERIC) {
            String s = cell.getNumericCellValue() + "";
            // 去掉尾巴上的小数点0
            if (Pattern.matches(".*\\.0*", s)) {
                return s.split("\\.")[0];
            } else {
                return s;
            }
        }
        // 布尔值类型
        if (cell.getCellType() == CellType.BOOLEAN) {
            return cell.getBooleanCellValue() + "";
        }
        // 错误类型
        return cell.getCellFormula();
    }

    private static String getString(String s) {
        if (s == null) {
            return "";
        }
        if (s.isEmpty()) {
            return s;
        }
        return s.trim();
    }

    /**
     * 复制模板文件 并输出内容至新文件
     *
     * @param template
     * @param targetFile
     * @param conn
     * @param versionCode
     * @return
     */
    public boolean exportExcelByCopyFiles(String template, String targetFile, Connection conn, String versionCode) {
        try {
            this.copyFile(template, targetFile);
            this.fillDataToExcel(targetFile, conn, versionCode);
            return true;
        } catch (Exception e) {
            e.printStackTrace();
            return false;
        }
    }

    /**
     * 通过英文字母获得列号
     *
     * @param colIndexStr
     * @return
     */
    public static int convertColIndexString2Number(String colIndexStr) {
        int len = colIndexStr.toUpperCase().length();
        char[] chars = colIndexStr.toCharArray();
        int col = 0;
        for (int index = 0; index < len; index++) {
            char ca = chars[index];
            int charAInt = Character.getNumericValue('A');
            int charInt = Character.getNumericValue(ca);
            BigDecimal bg = new BigDecimal(26);
            col = col + bg.pow(len - index - 1).intValue() * (charInt - charAInt + 1);
        }
        return col;
    }

    /**
     * 通过columnIndex获取英文列号
     *
     * @param colIndex
     * @return
     */
    public static String convertColIndexNumber2String(int colIndex) {
        if (colIndex >= 1 && colIndex <= 26) {
            return String.valueOf((char) (colIndex + 64));
        }
        if (colIndex >= 26) {
            int cNum = (colIndex - 1) % 26 + 1;
            String cRow = String.valueOf((char) (cNum + 64));

            String bRow = "";
            String aRow = "";
            int front = (colIndex - 1) / 26;
            if (front > 26) {
                int bNum = (front - 1) % 26 + 1;
                bRow = String.valueOf((char) (bNum + 64));
                int aNum = (front - 1) / 26;
                aRow = String.valueOf((char) (aNum + 64));
            } else {
                aRow = String.valueOf((char) (front + 64));
            }
            return aRow + bRow + cRow;
        }

        return null;
    }

    /**
     * 判断是否为数字. 整数位9,小数位4,包括负数. 以.开头的4位小数也判断为数字,如.1234 3位小数
     */
    public static boolean isNum(String str) {
        boolean flag1 = false;
        boolean flag2 = false;
        if (str != null && !"".equals(str = str.trim())) {
            str = str.replaceFirst("-", "").trim();
            if (str.indexOf(".") != -1) {
                String[] strs = str.split("\\.");
                if (strs.length == 2) {
                    flag1 = strs[0].matches("^[0-9]\\d{0,8}$") || "".equals(strs[0]);
                    flag2 = strs[1].matches("^[0-9]\\d{0,3}$");
                }
            } else {
                flag1 = str.matches("^[1-9]\\d{0,8}$|[0]");
                flag2 = true;
            }
        }
        return flag1 && flag2;
    }

    /**
     * 根据参数生成excel文件,生成07版本
     *
     * @param title      excel文件名
     * @param headColumn format: col1,col2,col3,
     * @param sql        填充内容的sql
     * @param hideCols   可以为空,需要隐藏的列 format:0,1,2,3...
     * @return
     */
    public Workbook createExcel(String title, String headColumn, String sql, String hideCols, Connection conn) {
        long start = System.currentTimeMillis();
        log.info("正在创建Excel文件...");
        PreparedStatement ps = null;
        Statement stmt = null;
        ResultSet rs = null;
        ResultSet rs2 = null;
        try {
            // conn = jdbcTemplate.getDataSource().getConnection();
            ps = conn.prepareStatement(sql);
            stmt = conn.createStatement();
            rs = ps.executeQuery();
            // sql = sql.toUpperCase();
            sql = sql.replaceAll("\n", " ");
            String countSql = "SELECT COUNT(*) FROM ( " + sql + ") as foo";
            rs2 = stmt.executeQuery(countSql);

            int total = 0;
            if (rs2.next()) {
                total = rs2.getInt(1);
            }
            rs2.close();
            stmt.close();

            // 获取结果集列数
            ResultSetMetaData metadata = rs.getMetaData();
            int colCount = metadata.getColumnCount();

            // 计算需要sheet的数量 //每页输出 500000条记录
            int sheets = (int) Math.ceil(total / 500000f);
            // 没有记录时输出表头
            if (sheets == 0)
                sheets = 1;
            Workbook wb = new HSSFWorkbook();
            String[] headCols = null;
            int sRow = 0;
            if (WebUtils.checkStr(headColumn)) {
                headCols = headColumn.split(",");
            } else {
                for (int i = 1; i <= metadata.getColumnCount(); i++) {
                    String columnName = metadata.getColumnName(i);
                    headColumn = headColumn + columnName + ",";
                }
                headCols = headColumn.split(",");
            }
            String[] hideColNum = {};
            if (hideCols != null) {
                hideColNum = hideCols.split(",");
            }

            for (int i = 0; i < sheets; i++) {
                Sheet sheet = wb.createSheet(title.split("\\.")[0] + i);
                //// excel 表头行
                if (headCols != null) {
                    Row row = sheet.createRow(0);
                    sRow = 1;
                    for (int j = 0; j < headCols.length; j++) {
                        Cell cell = row.createCell(j);
//                        cell.setCellType(CellType.CELL_TYPE_STRING);
                        cell.setCellValue(headCols[j]);
                    }
                }

                // 填充内容 每页内容为500000行
                int k = 0;
                while (rs.next()) {
                    Row content = sheet.createRow(k + sRow);
                    for (int l = 0; l < colCount; l++) {
                        Cell cell = content.createCell(l);
                        String str = rs.getString(l + 1);
//                        cell.setCellType(HSSFCell.CELL_TYPE_STRING);
                        cell.setCellValue(str);
//						if (isNum(str)) {
//							cell.setCellValue(Double.parseDouble(str));
//						} else {
//							cell.setCellValue(str);
//						}
                    }
                    k++;
                    if (k % 500000 == 0) {
                        break;
                    }
                }
                // 设置隐藏列
                for (int n = 0; n < hideColNum.length; n++) {
                    sheet.setColumnHidden(Integer.parseInt(hideColNum[n]), true);
                }
            }
            long end = System.currentTimeMillis();
            log.info("创建excel文件完毕,耗时:" + (end - start) + "毫秒.");
            //System.gc();
            return wb;
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DBUtils.close(ps, rs);
            DBUtils.close(conn);
        }
        return null;
    }

    /**
     * 根据参数生成excel文件并下载
     *
     * @param title      excel文件名
     * @param headColumn format: col1,col2,col3,
     * @param sql        填充内容的sql
     * @param hideCols   可以为null,需要隐藏的列 format:0,1,2,3...
     * @return
     */
    public boolean outPutExcelFile(HttpServletResponse response, String title, String headColumn, String sql,
                                   String hideCols, Connection connection) throws Exception {

        log.info("报表" + sql);
        Workbook wb = createExcel(title, headColumn, sql, hideCols, connection);
        try {
            if (title.indexOf(".") == -1) {
                title += ".xlsx";
            }
//            response.reset();
            response.setContentType("application/x-msdownload");
            response.addHeader("Content-Disposition",
                    "attachment; filename=\"" + new String(title.getBytes("gbk"), "ISO-8859-1") + "\"");
            ServletOutputStream out = response.getOutputStream();
            wb.write(out);
            out.flush();
            response.flushBuffer();
            out.close();
            //System.gc();
            return true;
        } catch (IOException e) {
            e.printStackTrace();
            throw new Exception("报表文件生成失败,请重新尝试!");
        }
    }

    private Integer impExcelIsData(Connection conn, String version, String sql, InputStream in, Integer sheetNum, int startRow)
            throws Exception {
        Integer count = 0;
        PreparedStatement ps = null;
        try {
            ps = conn.prepareStatement(sql);
            // 参数个数
            int paramCount = sql.split("\\?", -1).length - 1;

            SimpleDateFormat smdf = new SimpleDateFormat("yyyy-MM-dd");
            BufferedInputStream bis = new BufferedInputStream(in);
            Workbook wb = null;

            // 出错时默认使用03版本
            try {
                if (".xlsx".equalsIgnoreCase(version)) {
                    wb = new XSSFWorkbook(bis);
                } else {
                    wb = new HSSFWorkbook(bis);
                }
            } catch (Exception e) {
                e.printStackTrace();
                wb = new HSSFWorkbook(bis);
            }

            int sheets = 1;
            if (sheetNum != null && sheetNum != 0) {
                sheets = wb.getNumberOfSheets();
            }
            log.info("文件的Sheet数量为:" + sheets);

            for (int i = 0; i < sheets; i++) {
                Sheet sheet = wb.getSheetAt(i);
                int rowNum = sheet.getLastRowNum();
                count += (rowNum - 1);
                // 第一行为表头
                for (int j = startRow; j <= rowNum; j++) {
                    Row row = sheet.getRow(j);
                    if (row == null) {
                        continue;
                    } else {
                        System.out.println(row.getLastCellNum());
                        for (int k = 0; k < paramCount; k++) {
                            Cell cell = row.getCell(k);
                            if (cell != null) {
                                CellType cellType = cell.getCellType();
                                String value = "";
                                switch (cellType) {
                                    case NUMERIC:
                                        if (HSSFDateUtil.isCellDateFormatted(cell)) {
                                            double cellVal = cell.getNumericCellValue();
                                            Date d1 = HSSFDateUtil.getJavaDate(cellVal);
                                            if (cellVal < 24) {
                                                smdf.applyPattern("HH:mm:ss");
                                            } else {
                                                smdf.applyPattern("yyyy-MM-dd");
                                            }
                                            value = smdf.format(d1);
                                        } else {
                                            value = String
                                                    .valueOf(BigDecimal.valueOf(cell.getNumericCellValue()));
                                        }
                                        break;
                                    case FORMULA:
                                        try {
                                            value = cell.getStringCellValue();
                                        } catch (IllegalStateException e) {
                                            value = String.valueOf(cell.getNumericCellValue());
                                        }

                                        break;
                                    case STRING:
                                        // 处理例如 2012-7-4日期字符串
                                        String[] cVal = cell.toString().split("-");
                                        if (cVal.length == 3) {
                                            value += cVal[0];
                                            if (cVal[1].length() == 1) {
                                                value += "-" + cVal[1];
                                            } else {
                                                value += "-" + cVal[1];
                                            }
                                            if (cVal[2].length() == 1) {
                                                value += "-" + cVal[2];
                                            } else {
                                                value += "-" + cVal[2];
                                            }

                                        } else {
                                            value = cell.toString().trim();
                                        }
                                        break;
                                    default:
                                        value = cell.toString().trim();
                                }
                                // 替换全角空格
                                value = value.replaceAll(" ", "");
                                ps.setString(k + 1, value);
                            } else {
                                ps.setString(k + 1, "");
                            }
                        }
                        ps.addBatch();
                    }
                    if (j % 3000 == 0) {
                        ps.executeBatch();
                        ps.clearBatch();
                    }
                }
                // 每个sheet保存一次.
                ps.executeBatch();
            }
            log.info("导入文件成功!");
        } catch (Exception e) {
            e.printStackTrace();
            //throw new Exception("导入失败!");
        } finally {
            if (ps != null) {
                ps.close();
            }
        }
        return count;
    }

    /**
     * 支持03和07以上版本 version 使用文件后缀 .xls 和.xlsx,默认是.xls 默认excel文件都有个表头,使用in 文件流
     * excel sheet的数量,可为null
     */
    private Integer impExcelIsData(Connection conn, String version, String sql, InputStream in, Integer sheetNum, int startRow, boolean isInputSeq)
            throws Exception {
        Integer count = 0;
        PreparedStatement ps = null;
        try {
            ps = conn.prepareStatement(sql);
            // 参数个数
            int paramCount = sql.split("\\?", -1).length - 1;

            SimpleDateFormat smdf = new SimpleDateFormat("yyyy-MM-dd");
            BufferedInputStream bis = new BufferedInputStream(in);
            Workbook wb = null;

            // 出错时默认使用03版本
            try {
                if (".xlsx".equalsIgnoreCase(version)) {
                    wb = new XSSFWorkbook(bis);
                } else {
                    wb = new HSSFWorkbook(bis);
                }
            } catch (Exception e) {
                e.printStackTrace();
                wb = new HSSFWorkbook(bis);
            }

            int sheets = wb.getNumberOfSheets();
            if (sheetNum != null && sheetNum != 0) {
                sheets = sheetNum;
            } else {
                sheets = 1;
            }
            log.info("文件的Sheet数量为:" + sheets);

            int importSeq = 0;
            for (int i = 0; i < sheets; i++) {
                Sheet sheet = wb.getSheetAt(i);
                int rowNum = sheet.getLastRowNum();
                count += (rowNum - 1);
                // 第一行为表头
                for (int j = startRow; j <= rowNum; j++) {
                    System.out.println(j);
                    Row row = sheet.getRow(j);
                    importSeq++;
                    if (row == null) {
                        continue;
                    } else {

                        for (int k = 0; k < paramCount; k++) {

                            if (k == paramCount - 1) {
                                ps.setInt(k + 1, importSeq);
                            } else {

                                Cell cell = row.getCell(k);
                                if (cell != null) {
                                    CellType cellType = cell.getCellType();
                                    String value = "";
                                    switch (cellType) {
                                        case NUMERIC:
                                            if (HSSFDateUtil.isCellDateFormatted(cell)) {
                                                double cellVal = cell.getNumericCellValue();
                                                Date d1 = HSSFDateUtil.getJavaDate(cellVal);
                                                if (cellVal < 24) {
                                                    smdf.applyPattern("HH:mm:ss");
                                                } else {
                                                    smdf.applyPattern("yyyy-MM-dd");
                                                }
                                                value = smdf.format(d1);
                                            } else {
                                                value = String
                                                        .valueOf(BigDecimal.valueOf(cell.getNumericCellValue()).doubleValue());
                                            }
                                            break;
                                        case FORMULA:
                                            try {
                                                value = cell.getStringCellValue();
                                            } catch (IllegalStateException e) {
                                                value = String.valueOf(cell.getNumericCellValue());
                                            }

                                            break;
                                        case STRING:
                                            // 处理例如 2012-7-4日期字符串
                                            value = cell.toString().trim();
                                            break;
                                        default:
                                            value = cell.toString().trim();
                                    }
                                    // 替换全角空格
                                    value = value.replaceAll(" ", "");
                                    ps.setString(k + 1, value);
                                } else {
                                    ps.setString(k + 1, "");
                                }
                            }
                        }
                        ps.addBatch();
                    }
                    if (j % 3000 == 0) {
                        ps.executeBatch();
                        ps.clearBatch();
                    }
                }
                // 每个sheet保存一次.
                ps.executeBatch();
            }
            log.info("导入文件成功!");
        } catch (Exception e) {
            e.printStackTrace();
            //throw new Exception("导入失败!");
        } finally {
            if (ps != null) {
                ps.close();
            }
        }
        return count;
    }

    /**
     * 默认excel文件都有个表头,使用request输入流 这个为公用方法 excel sheet的数量,可为null
     */
    public Integer impExcelData(Connection conn, String sql, HttpServletRequest request, Integer sheetNum, int startRow)
            throws Exception {
        Integer count = 0;
        try {
            MultipartHttpServletRequest multiRequest = (MultipartHttpServletRequest) request;
            Map<String, MultipartFile> files = multiRequest.getFileMap();
            log.info("文件总数" + files.size());
            for (MultipartFile file : files.values()) {
                // 读取excel,默认为xls
                String filePath = file.getOriginalFilename();
                int index = filePath.lastIndexOf(".");
                String suffix = ""; // 文件后缀
                if (index != -1) {
                    suffix = filePath.substring(index, filePath.length());
                }
                count = this.impExcelIsData(conn, suffix, sql, file.getInputStream(), sheetNum, startRow);
                log.info("导入文件" + file.getOriginalFilename() + "成功!");
            }
        } catch (Exception e) {
            e.printStackTrace();
            throw new Exception("导入失败!");
        }
        return count;
    }


    /**
     * 默认excel文件都有个表头,使用request输入流 这个为公用方法 excel sheet的数量,可为null
     */
    public Integer impExcelData(Connection conn, String sql, HttpServletRequest request, boolean isInputSeq, Integer sheetNum, int startRow)
            throws Exception {
        Integer count = 0;
        try {
            MultipartHttpServletRequest multiRequest = (MultipartHttpServletRequest) request;
            Map<String, MultipartFile> files = multiRequest.getFileMap();
            log.info("文件总数" + files.size());
            for (MultipartFile file : files.values()) {
                // 读取excel,默认为xls
                String filePath = file.getOriginalFilename();
                int index = filePath.lastIndexOf(".");
                String suffix = ""; // 文件后缀
                if (index != -1) {
                    suffix = filePath.substring(index, filePath.length());
                }
                count = this.impExcelIsData(conn, suffix, sql, file.getInputStream(), sheetNum, startRow, isInputSeq);
                log.info("导入文件" + file.getOriginalFilename() + "成功!");
            }
        } catch (Exception e) {
            e.printStackTrace();
            throw new Exception("导入失败!");
        }
        return count;
    }


    /**
     * 默认excel文件都有个表头,使用dataFile 这个为公用方法 excel sheet的数量,可为null
     *
     * @param conn
     * @param sql  插入sql
     * @throws Exception
     */
    public Integer impExcelData(Connection conn, String sql, File dataFile, Integer sheetNum, int startRow) throws Exception {
        Integer count = 0;
        try {
            if (dataFile != null) {
                InputStream in = new FileInputStream(dataFile);
                // 读取excel,默认为xls
                String filePath = dataFile.getName();
                int index = filePath.lastIndexOf(".");
                String suffix = ""; // 文件后缀
                if (index != -1) {
                    suffix = filePath.substring(index, filePath.length());
                }
                count = this.impExcelIsData(conn, suffix, sql, in, sheetNum, startRow);
                log.info("导入文件" + dataFile.getName() + "成功!");
            }
        } catch (Exception e) {
            e.printStackTrace();
            throw new Exception("导入失败!");
        }
        return count;
    }

}
